﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using DbHelper;
using Model;

namespace DAL
{
    public class MySubscribeDal
    {
        /// <summary>
        /// 我的商品列表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<MySubscribe> ListMyGoods(MySubscribe model)
        {
            List<MySubscribe> list = new List<MySubscribe>();
            string sql = "select m.id,m.goodsid,classid,(select className from goodsClassfy where id=g.classId) as className,isneedshow,showinnum,goodsname,picsrc,no,leftnum,lastupdatetime,note,aliww,shopname,shopurl from mysubscribe m left join goodsinfo g on m.goodsid=g.goodsid left join seller s on g.sellerId=s.id where m.sellerid=@sellerid ";
            if (model.GoodsInfo.ClassId > 0)
            {
                sql += " and classId=@classId ";
            }
            if (!string.IsNullOrEmpty(model.GoodsInfo.GoodsName))
            {
                sql += " and goodsName like '%" + model.GoodsInfo.GoodsName + "%' ";
            }
            if (!string.IsNullOrEmpty(model.GoodsInfo.No))
            {
                sql += " and no=@no ";
            }
            if (!string.IsNullOrEmpty(model.SellerInfo.Aliww))
            {
                sql += " and aliww=@aliww ";
            }
            SQLiteParameter[] sqliteParameter = new SQLiteParameter[]{
                new SQLiteParameter("@sellerid",model.SellerId),
                new SQLiteParameter("@classId",model.GoodsInfo.ClassId),
                new SQLiteParameter("@no",model.GoodsInfo.No),
                new SQLiteParameter("@aliww",model.SellerInfo.Aliww),
            };
            SQLiteDataReader sdr = DbHelperSQLite.ExecuteReader(sql, sqliteParameter);
            while (sdr.Read())
            {
                MySubscribe mg = new MySubscribe();
                mg.Id = Convert.ToInt32(sdr["id"]);
                mg.GoodsId = Convert.ToString(sdr["goodsId"]);
                mg.GoodsInfo.ClassId = Convert.ToInt32(sdr["classid"]);
                mg.IsNeedShow = Convert.ToBoolean(sdr["isneedshow"]);
                mg.ShowInNum = Convert.ToInt32(sdr["showinnum"]);
                mg.GoodsInfo.GoodsName = Convert.ToString(sdr["goodsname"]);
                mg.GoodsInfo.PicSrc = Convert.ToString(sdr["picsrc"]);
                mg.GoodsInfo.No = Convert.ToString(sdr["no"]);
                mg.GoodsInfo.LeftNum = Convert.ToInt32(sdr["leftnum"]);
                mg.GoodsInfo.LastUpdateTime = Convert.ToDateTime(sdr["lastupdatetime"]);
                mg.GoodsInfo.Note = Convert.ToString(sdr["note"]);
                mg.GoodsInfo.ClassName = Convert.ToString(sdr["className"]);
                mg.SellerInfo.Aliww = Convert.ToString(sdr["aliww"]);
                mg.SellerInfo.ShopName = Convert.ToString(sdr["shopName"]);
                mg.SellerInfo.ShopUrl = Convert.ToString(sdr["shopUrl"]);
                list.Add(mg);
            }
            return list;
        }

        /// <summary>
        /// 添加到我的商品库
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool AddToMyGoods(List<MySubscribe> list)
        {
            using (SQLiteConnection conn = new SQLiteConnection(DbHelperSQLite.connectionString))
            {
                conn.Open();
                SQLiteTransaction trans = conn.BeginTransaction();
                try
                {
                    string sql = "insert into mysubscribe (sellerid,goodsid,isneedshow,showinnum,hadShowDtlIds,addtime) values (@sellerid,@goodsid,0,0,0,datetime('now','localtime'))";
                    SQLiteParameter[] sqliteParameter;
                    foreach (var li in list)
                    {
                        sqliteParameter = new SQLiteParameter[]{
                            new SQLiteParameter("@sellerid",li.SellerId),
                            new SQLiteParameter("@goodsid",li.GoodsId),
                        };
                        DbHelperSQLite.ExecuteSql(trans, CommandType.Text, sql, sqliteParameter);
                    }
                    trans.Commit();
                    return true;
                }
                catch
                {
                    trans.Rollback();
                    return false;
                }
            }
        }

        /// <summary>
        /// 加入弹窗消息
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool AddToShow(MySubscribe m)
        {
            using (SQLiteConnection conn = new SQLiteConnection(DbHelperSQLite.connectionString))
            {
                conn.Open();
                SQLiteTransaction trans = conn.BeginTransaction();
                try
                {
                    string sql = "update mysubscribe set isneedshow=@isneedshow,showinnum=@showinnum where id=@id";
                    SQLiteParameter[] sqliteParameter;
                    sqliteParameter = new SQLiteParameter[]{
                        new SQLiteParameter("@isneedshow",m.IsNeedShow),
                        new SQLiteParameter("@showinnum",m.ShowInNum),
                        new SQLiteParameter("@id",m.Id),
                    };
                    DbHelperSQLite.ExecuteSql(trans, CommandType.Text, sql, sqliteParameter);
                    trans.Commit();
                    return true;
                }
                catch
                {
                    trans.Rollback();
                    return false;
                }
            }
        }

        /// <summary>
        /// 删除弹窗消息
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool DeleteShow(List<MySubscribe> list)
        {
            using (SQLiteConnection conn = new SQLiteConnection(DbHelperSQLite.connectionString))
            {
                conn.Open();
                SQLiteTransaction trans = conn.BeginTransaction();
                try
                {
                    string sql = "update mysubscribe set isneedshow=0,showinnum=0 where id=@id";
                    SQLiteParameter[] sqliteParameter;
                    foreach (var li in list)
                    {
                        sqliteParameter = new SQLiteParameter[]{
                            new SQLiteParameter("@id",li.Id),
                        };
                        DbHelperSQLite.ExecuteSql(trans, CommandType.Text, sql, sqliteParameter);
                    }
                    trans.Commit();
                    return true;
                }
                catch
                {
                    trans.Rollback();
                    return false;
                }
            }
        }

        /// <summary>
        /// 删除我的商品信息
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool DeleteMyGoods(List<MySubscribe> list)
        {
            using (SQLiteConnection conn = new SQLiteConnection(DbHelperSQLite.connectionString))
            {
                conn.Open();
                SQLiteTransaction trans = conn.BeginTransaction();
                try
                {
                    string sql = "delete from mysubscribe where id=@id";
                    SQLiteParameter[] sqliteParameter;
                    foreach (var li in list)
                    {
                        sqliteParameter = new SQLiteParameter[]{
                            new SQLiteParameter("@id",li.Id),
                        };
                        DbHelperSQLite.ExecuteSql(trans, CommandType.Text, sql, sqliteParameter);
                    }
                    trans.Commit();
                    return true;
                }
                catch
                {
                    trans.Rollback();
                    return false;
                }
            }
        }

        /// <summary>
        /// 添加明细id到hadShowDtlIds
        /// </summary>
        /// <param name="id"></param>
        /// <param name="dtlId"></param>
        /// <returns></returns>
        public bool UpdateShowStatus(int id,string dtlId)
        {
            string sql = "update mysubscribe set hadShowDtlIds=hadShowDtlIds+@dtlId where id=@id";
            SQLiteParameter[] sqliteParameter;
            sqliteParameter = new SQLiteParameter[]{
                new SQLiteParameter("@dtlId",dtlId),
                new SQLiteParameter("@id",id)
            };
            if (DbHelperSQLite.ExecuteSql(sql, sqliteParameter) == 0)
            {
                return false;
            }
            return true;

        }

        public DataTable MyGoodsShowDt(int sellerId)
        {
            DataTable dt = null;
            string sql = "select m.id,m.goodsid,classid,(select className from goodsClassfy where id=g.classId) as className,isneedshow,showinnum,goodsname,picsrc,no,leftnum,lastupdatetime,note,aliww,shopname,shopurl,cnt,color,size,colorpicsrc from mysubscribe m left join goodsinfo g on m.goodsid=g.goodsid left join seller s on g.sellerId=s.id inner join goodsdtlinfo dtl on g.goodsid=dtl.goodsid where m.isneedshow=1 and dtl.cnt<=m.showinnum and (dtl.hadshow=0 or dtl.hadshow is null)";

            dt = DbHelperSQLite.Query(sql).Tables[0];
            return dt;
        }
    }
}
